﻿Imports System.Data

Partial Class UCMain_SumTP_request
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If IsNothing(Session("section")) Then
            Session.Clear()
            Response.Redirect("../Login.aspx")
        End If
        If Not IsPostBack Then

        End If
    End Sub

    Protected Sub BtnSearch_Click(sender As Object, e As EventArgs) Handles BtnSearch.Click
        loadData()
    End Sub


    Sub loadData()
        Dim sql As String = ""
        sql += "SELECT Request_table.ID_REQUEST_FORM,Request_table.DUR_WORK_FORM,Request_table.DUR_WORK_TO,Request_table.WORK_LOCA_E, Request_table.WORK_LOCA_W, Request_table.WORK_LOCA_FROM, Request_table.WORK_LOCA_TO "
        sql += ",Switch(Request_table.WORK_LOCA_W='1','West') as work_localW"
        sql += ",Switch(Request_table.WORK_LOCA_E='1','East')  as work_localE "

        sql += ",Switch(Request_table.WORK_LOCA_SPECIFY='1','from '+(select station+' '+ area "
        sql += "from station where id=Request_table.WORK_LOCA_FROM) +' to ' + (select station+' '+ area from station where id=Request_table.WORK_LOCA_To )"

        sql += ",WORK_LOCA_SPECIFY='2','from ' + (select signal from [signal] where id = Request_table.WORK_LOCA_FROM) +' to ' +"
        sql += "(select signal from [signal] where id = Request_table.WORK_LOCA_TO)) as st_si "

        sql += ",Switch(Request_table.WORK_LOCA_W='1' and WORK_LOCA_E='1' and WORK_LOCA_SPECIFY='2' ,'from West '+"
        sql += "(select station+' '+ area from station where id=Request_table.WORK_LOCA_FROM) +' to ' + "
        sql += "(select station+' '+ area from station where id=Request_table.WORK_LOCA_TO) + ' - from East ' +"
        sql += "(select station+' '+ area from station where id=Request_table.WORK_LOCA_FROM_S) +' to ' +"
        sql += "(select station+' '+ area from station where id=Request_table.WORK_LOCA_TO_S)  ) as WE ,DESC_OD_WORK as desc_work "

        sql += ",Switch(POWER_REQ='0','Yes') as energ_req ,Switch(POWER_REQ='0',E1+' '+E2+' '+E3+' '+E4+' '+E5+' '+E6+' '+W1+' '+W2+' '+W3+' '+W4+' '+W5+' '+W6) as energ "
        sql += ",Switch(TRAIN_REQ='1','Yes',TRAIN_REQ='0','No') as train "
        sql += ",Switch(VIHECLES_REQ='1','Yes',VIHECLES_REQ='0','No') as vihecle "

        sql += ",u.[section] as sc  ,Request_table.STATUS_APP as st"
        sql += ",Request_table.TP_NO  "


        sql += ", IIf(IsNull(V_1.Vehicles),'',IIf(V_1.Vehicles='','',' '+V_1.Vehicles)) + IIf(IsNull(V_2.Vehicles),'',IIf(V_2.Vehicles='','',' '+V_2.Vehicles))+"
        sql += "IIf(IsNull(V_3.Vehicles),'',IIf(V_3.Vehicles='','',' '+V_3.Vehicles))+IIf(IsNull(V_4.Vehicles),'',IIf(V_4.Vehicles='','',' '+V_4.Vehicles))+"
        sql += "IIf(IsNull(V_5.Vehicles),'',IIf(V_5.Vehicles='','',' '+V_5.Vehicles))+IIf(IsNull(V_6.Vehicles),'',IIf(V_6.Vehicles='','',' '+V_6.Vehicles))+"
        sql += "IIf(IsNull(V_7.Vehicles),'',IIf(V_7.Vehicles='','',' '+V_7.Vehicles)) AS Vihacles,"

        sql += "IIf(IsNull(T_1.Vehicles),'',IIf(T_1.Vehicles='','',' '+T_1.Vehicles))+IIf(IsNull(T_2.Vehicles),'',IIf(T_2.Vehicles='','',' '+T_2.Vehicles))+"
        sql += "IIf(IsNull(T_3.Vehicles),'',IIf(T_3.Vehicles='','',' '+T_3.Vehicles))+IIf(IsNull(T_4.Vehicles),'',IIf(T_4.Vehicles='','',' '+T_4.Vehicles))+"
        sql += "IIf(IsNull(T_5.Vehicles),'',IIf(T_5.Vehicles='','',' '+T_5.Vehicles))+IIf(IsNull(T_6.Vehicles),'',IIf(T_6.Vehicles='','',' '+T_6.Vehicles))+"
        sql += "IIf(IsNull(T_7.Vehicles),'',IIf(T_7.Vehicles='','',' '+T_7.Vehicles))+IIf(IsNull(T_8.Vehicles),'',IIf(T_8.Vehicles='','',' '+T_8.Vehicles))+"
        sql += "IIf(IsNull(T_9.Vehicles),'',IIf(T_9.Vehicles='','',' '+T_9.Vehicles))  AS Trains"

        sql += " FROM  user_table AS u  INNER JOIN (Vehicles AS V_7 RIGHT JOIN (Vehicles AS V_6 RIGHT JOIN (Vehicles AS V_5 RIGHT JOIN "
        sql += "(Vehicles AS V_4 RIGHT JOIN (Vehicles AS V_3 RIGHT JOIN (Vehicles AS V_2 RIGHT JOIN (Vehicles AS V_1 RIGHT JOIN "
        sql += "(Vehicles AS T_9 RIGHT JOIN (Vehicles AS T_8 RIGHT JOIN (Vehicles AS T_7 RIGHT JOIN (Vehicles AS T_6 RIGHT JOIN "
        sql += "(Vehicles AS T_5 RIGHT JOIN (Vehicles AS T_4 RIGHT JOIN (Vehicles AS T_3 RIGHT JOIN (Vehicles AS T_2 RIGHT JOIN"
        sql += "(Request_table LEFT JOIN Vehicles AS T_1 ON Request_table.T1 = T_1.ID) ON T_2.ID = Request_table.T2) ON T_3.ID = Request_table.T3)"
        sql += "ON T_4.ID = Request_table.T4) ON T_5.ID = Request_table.T5) ON T_6.ID = Request_table.T6) ON T_7.ID = Request_table.T7) "
        sql += "ON T_8.ID = Request_table.T8) ON T_9.ID = Request_table.T9) ON V_1.ID = Request_table.V1) ON V_2.ID = Request_table.V2) "
        sql += "ON V_3.ID = Request_table.V3) ON V_4.ID = Request_table.V4) ON V_5.ID = Request_table.V5) ON V_6.ID = Request_table.V6)"
        sql += " ON V_7.ID = Request_table.V7) ON Request_table.REQ_ID_PICOP = u.ID_USER where 1=1"

        If Session("section") <> "MMC" And rdoAP.SelectedValue = "1" Then
            sql += "and u.[section]='" & Session("section") & "'"
        End If



        If rdoAP.SelectedValue <> "2" Then
            sql += " and Request_table.STATUS_APP='" & rdoAP.SelectedValue & "'"
        End If

        If txtDateStart.Text <> "" And txtDateEnd.Text <> "" Then
            sql += " and ( format(cdate(Request_table.DUR_WORK_FORM),'yyyyMMdd') >= '" & Utility.clscheckDatetime.ConvertDateTimeDBEngSearch(txtDateStart.SelectedDate) & "' and format(cdate(Request_table.DUR_WORK_TO),'yyyyMMdd') <= '" & Utility.clscheckDatetime.ConvertDateTimeDBEngSearch(txtDateEnd.SelectedDate) & "')"
        End If

        Dim dt As New Data.DataTable
        dt = clsCommand.SQL_exec(sql)

        GridView1.DataSource = dt
        GridView1.DataBind()

        If Session("section") <> "MMC" Then
            GridView1.Enabled = False
        Else
            GridView1.Enabled = True
        End If


    End Sub

    Protected Sub GridView1_RowCommand(sender As Object, e As GridViewCommandEventArgs) Handles GridView1.RowCommand
        Try
            If e.CommandName.ToLower = "bedit" Then
                Response.Redirect("Track_applicationRequest.aspx?ID_REQUEST_FORM=" & e.CommandArgument)
            ElseIf e.CommandName.ToLower = "approve" Then
                Response.Redirect("Track_applicationRequest.aspx?ID_REQUEST_FORM=" & e.CommandArgument & "&case=view")
            End If
        Catch ex As Exception

        End Try
    End Sub

    Protected Sub GridView1_RowDataBound(sender As Object, e As GridViewRowEventArgs) Handles GridView1.RowDataBound
        Try
            Select Case e.Row.RowType
                Case DataControlRowType.DataRow
                    Dim lblst As Label = e.Row.FindControl("lblst")
                    Dim imgBAP As ImageButton = e.Row.FindControl("imgBAP")
                    If Not IsNothing(lblst) Then
                        If lblst.Text = "1" Then
                            imgBAP.Enabled = False
                            imgBAP.ImageUrl = "~/img/stAP.png"
                        Else
                            imgBAP.Enabled = True
                        End If
                    End If

                    Dim lblID As Label = e.Row.FindControl("lblID")
                    Dim lblEast As Label = e.Row.FindControl("lblEast")
                    Dim lblWest As Label = e.Row.FindControl("lblWest")
                    Dim lblFrom As Label = e.Row.FindControl("lblFrom")
                    Dim lblTo As Label = e.Row.FindControl("lblTo")
                    Dim Work As String = e.Row.Cells(4).Text
                    Dim dates() As String = Work.Split("/")
                    Dim WorkDate As New DateTime(dates(2), dates(1), dates(0))
                    Dim UStart As Integer = CInt(lblFrom.Text)
                    Dim UEnd As Integer = CInt(lblTo.Text)
                    Dim sql As String
                    Dim dt As New Data.DataTable

                    Dim IsEastConflict = False
                    Dim IsWestConflict = False

                    If lblEast.Text = "1" Then

                        sql = "SELECT DUR_WORK_FORM, WORK_LOCA_E, WORK_LOCA_FROM, WORK_LOCA_TO from Request_table WHERE Not ID_REQUEST_FORM=" & lblID.Text & " AND WORK_LOCA_E='1' AND CDate(DUR_WORK_FORM)=CDate('" & WorkDate.AddYears(-543).ToString("yyyy/MM/dd") & "')  AND STATUS_APP = '1'"
                        dt = clsCommand.SQL_exec(sql)

                        For Each row As DataRow In dt.Rows
                            Dim EStart As Integer = CInt(row("WORK_LOCA_FROM"))
                            Dim EEnd As Integer = CInt(row("WORK_LOCA_TO"))

                            If (UStart >= EStart And UStart <= EEnd) And (UEnd >= EStart And UEnd <= EEnd) _
                                Or (UStart >= EStart And UStart <= EEnd) Or (UEnd >= EStart And UEnd <= EEnd) Then
                                IsEastConflict = True
                                Exit For
                            End If
                        Next
                    End If

                    If lblWest.Text = "1" And IsEastConflict = False Then
                        sql = "SELECT DUR_WORK_FORM, WORK_LOCA_W, WORK_LOCA_FROM, WORK_LOCA_TO from Request_table WHERE Not ID_REQUEST_FORM=" & lblID.Text & " AND WORK_LOCA_W='1' AND CDate(DUR_WORK_FORM)=CDate('" & WorkDate.AddYears(-543).ToString("yyyy/MM/dd") & "') AND STATUS_APP = '1'"
                        dt = clsCommand.SQL_exec(sql)

                        For Each row As DataRow In dt.Rows
                            Dim EStart As Integer = CInt(row("WORK_LOCA_FROM"))
                            Dim EEnd As Integer = CInt(row("WORK_LOCA_TO"))

                            If (UStart >= EStart And UStart <= EEnd) And (UEnd >= EStart And UEnd <= EEnd) _
                                Or (UStart >= EStart And UStart <= EEnd) Or (UEnd >= EStart And UEnd <= EEnd) Then
                                IsWestConflict = True
                                Exit For
                            End If
                        Next
                    End If

                    If IsEastConflict Or IsWestConflict Then
                        Dim lblConflict As Label = e.Row.FindControl("lblConflict")
                        lblConflict.Text = "Yes"
                    End If
            End Select
        Catch ex As Exception

        End Try
    End Sub

    Protected Sub GridView1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles GridView1.SelectedIndexChanged

    End Sub

    Protected Sub rdoAP_SelectedIndexChanged(sender As Object, e As EventArgs) Handles rdoAP.SelectedIndexChanged

    End Sub
End Class
